'''
author: zpj

服务端要部署pgbouncer连接池管理连接，应用层就没必要再使用连接池，直接使用psycopg2连接即可。

'''

import logging
import psycopg2 
from psycopg2.extras import RealDictCursor

# 配置日志记录
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')


class PostgreSQLConnection:

    def __enter__(self):
        return self.conn, self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        
        if exc_type:
            logging.error(f"数据库操作失败，错误类型: {exc_type}, 错误信息: {exc_val}")
            self.conn.rollback()
        else:
            self.conn.commit()
        self.cursor.close()
        self.conn.close()


    def __init__(self):

        self.conn = psycopg2.connect(
            user='baina_iot',
            password='e@R3#9yU7m%',
            host='47.96.3.242',
            port='7432', # pgbouncer端口
            database='baina_iot'
        )
        self.cursor = self.conn.cursor(cursor_factory=RealDictCursor)


if __name__ == '__main__':

    with PostgreSQLConnection() as (conn, cursor):
        cursor.execute("SELECT version();")
        version = cursor.fetchone()
        print(dict(version))

        cursor.execute("SELECT * FROM users;")
        users = cursor.fetchall()
        print(users)

